from flask import Flask

import db.db_config as config
import common.commonTime as commonTime

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

db = SQLAlchemy()

"""
初始化 数据库，并放回数据库对象
"""


def create_app():
    app = Flask(__name__)
    app.config.from_object(config.Config)
    # 关联app 和数据库
    db.init_app(app)
    return app


def get_corpus_from_db(time):
    start_time, end_time = commonTime.get_start_end_from_time(time)
    sql = "SELECT id,JSON_UNQUOTE(JSON_EXTRACT(original_data,'$.input1669626526806')),JSON_UNQUOTE(JSON_EXTRACT(original_data,'$.textarea1669626440853')) " \
          "FROM fm_user_form_data where  form_key LIKE :form_key and ext_value LIKE '0_%' and create_time >= :start_time and create_time <= :end_time "
    params = {"form_key": "gDPT6Rmx%", "start_time": start_time, "end_time": end_time}
    custom_sql = text(sql)
    result = db.session.execute(custom_sql, params).fetchall()
    corpus = []
    for (id, address, content) in result:
        if address is None or address == '':
            corpus.append({"id": id, "content": content, "origin_content": content})
        else:
            corpus.append({"id": id, "content": address + ":" + content, "origin_content": content})
    return corpus
